# -*- coding: UTF-8 -*-
import sqlite3
import os

def readFile(path):  #读取文件，返回列表
    listp=[]
    if os.path.exists(path):
        strc = ''
        with open(path,'r',encoding='UTF-8') as file:
            file.seek(0)
            lines=file.readlines()
            for i in lines:
                strc+=i
        listp=strc.split(';')
    return listp

def sqlexu(connio,sql,num='1'):
    result=[]
    connio.cursor()
    result=connio.execute(sql)
    if num=='2':
        connio.commit()
    return result


def tableExists():  #查询表是否存在，存在返回exists=1
    exists=0
    connion=sqlite3.connect('test.db')
    sql7="select name from sqlite_master where type='table' order by name"
    sql8='DROP TABLE FUNDS'
    list2=sqlexu(connion,sql7);
    for i in list2:
       if 'FUNDS' in i:
           exists=1
           break
    return exists

def createDb():
    connion=sqlite3.connect('test.db')
    #查询表是否已存在
    try:
        if tableExists()==1:
            sql1='DROP TABLE FUNDS'
            sqlexu(connion, sql1, '2')
        path=os.path.join(os.getcwd(),'FUNDS.sql')
        listp=readFile(path)
        for exq in listp:
            sqlexu(connion,exq,'2')
        sql2='select SHORTNAME from FUNDS order by SYL_5N desc limit 1'
        lo=sqlexu(connion,sql2)
        for row in lo:
            print('基金名称是：',row[0])
    except Exception as e:
        connion.rollback()
        print(e)
    finally:
        connion.close()


createDb()
